/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package restaurante.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import restaurante.bancodados.CriaConexao;
import restaurante.logica.Fornecedor;

/**
 *
 * @author Antonio
 */
public class FornecedorDao {
    private Connection conexao;

    public FornecedorDao() throws SQLException{
        this.conexao = CriaConexao.getConexao();
    }

    public void adiciona(Fornecedor f1) throws SQLException{
        String  sql = "insert into fornecedor (cnpj, nome, endereco, telefone, email)" +
                "values (?,?,?,?,?)";
        PreparedStatement stmt = conexao.prepareStatement(sql);

        // Seta os valores
        stmt.setString(1, f1.getCnpj());
        stmt.setString(2, f1.getNome());
        stmt.setString(3, f1.getEndereco());
        stmt.setString(4, f1.getTelefone());
        stmt.setString(5, f1.getEmail());

        // Executa o código SQL
        stmt.execute();
        stmt.close();
    }


    public  List<Fornecedor> getLista(String busca) throws SQLException{
        String sql = "Select * from fornecedor where nome like ?";
        PreparedStatement stmt = this.conexao.prepareStatement(sql);

        stmt.setString(1, busca);       // inserção do caracter de busca.

        ResultSet rs = stmt.executeQuery();

        List<Fornecedor> minhaLista = new ArrayList<Fornecedor>();

        while(rs.next()){
            Fornecedor f1 = new Fornecedor();
            f1.setId(rs.getInt("id"));
            f1.setCnpj(rs.getString("cnpj"));
            f1.setNome(rs.getString("nome"));
            f1.setEndereco(rs.getString("endereco"));
            f1.setTelefone(rs.getString("telefone"));
            f1.setEmail(rs.getString("email"));

            minhaLista.add(f1);
        }

        rs.close();
        stmt.close();
        return minhaLista;
    }

    public  List<Fornecedor> getViewTelefone(String busca) throws SQLException{
        String sql = "Select * from tel_fornecedor where nome like ?";
        PreparedStatement stmt = this.conexao.prepareStatement(sql);

        stmt.setString(1, busca);

        ResultSet rs = stmt.executeQuery();

        List<Fornecedor> minhaLista = new ArrayList<Fornecedor>();

        while(rs.next()){
            Fornecedor f1 = new Fornecedor();
            f1.setNome(rs.getString("nome"));
            f1.setTelefone(rs.getString("telefone"));
            minhaLista.add(f1);
        }

        rs.close();
        stmt.close();
        return minhaLista;
    }

    public void alteraEmailFornecedor(String cnpj,String emailNovo) throws SQLException{

        String sql = "call altera_email_fornecedor(?,?)";
        PreparedStatement stmt = conexao.prepareStatement(sql);

        stmt.setString(1, cnpj);
        stmt.setString(2, emailNovo);

        // Executa o código SQL
        stmt.execute();
        stmt.close();
    }

    public void altera(Fornecedor f1) throws SQLException{
        String sql = "update fornecedor set cnpj=?, nome=?, endereco=?" +
                        ", telefone=?, email=?" +
                        "where id=?";
        PreparedStatement stmt = conexao.prepareStatement(sql);

        // Seta os valores
        stmt.setString(1, f1.getCnpj());
        stmt.setString(2, f1.getNome());
        stmt.setString(3, f1.getEndereco());
        stmt.setString(4, f1.getTelefone());
        stmt.setString(5, f1.getEmail());
        stmt.setString(6, String.valueOf(f1.getId()));

        // Executa o código SQL
        stmt.execute();
        stmt.close();

    }

    public void remove(Fornecedor f1) throws SQLException{
        String sql = "delete from fornecedor where id=?";
        PreparedStatement stmt = conexao.prepareStatement(sql);

        stmt.setString(1, String.valueOf(f1.getId()));

        stmt.execute();
        stmt.close();

    }


}
